package id.co.shuba.spamobile.data;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class AreaDB extends BaseDB {		
	public static final int LVL_KODYA_KAB = 1;
	public static final int LVL_KECAMATAN = 2;
	public static final int LVL_KELURAHAN = 3;
	
	public AreaDB(DatabaseHandler db) {
		super(db);
	}
	
	public static void addPostalCode(SQLiteDatabase db, String postalCode,
			String kodya, String kecamatan, String kelurahan, String date, String aoCode) {

		ContentValues values = new ContentValues();
		values.put("POSTAL_CODE", postalCode);
		values.put("KODYA_KAB", kodya);
		values.put("KECAMATAN", kecamatan);
		values.put("KELURAHAN", kelurahan);
		values.put("UPDATE_DATE", date);
		values.put("AO_CODE", aoCode);

		db.insert("TBL_MS_USER_ZIP", null, values);
	}
	
	public List<String> getKodyaList(String aoCode) {
		String sql = "SELECT DISTINCT KODYA_KAB FROM TBL_MS_USER_ZIP WHERE AO_CODE = ?";

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] { aoCode });

		List<String> list = new ArrayList<String>();

		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}

		cursor.close();

		return list;
	}

	public List<String> getKecamatanList(String aoCode, String kodya) {
		String sql = "SELECT DISTINCT KECAMATAN FROM TBL_MS_USER_ZIP WHERE AO_CODE = ? AND KODYA_KAB = ?";

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] { aoCode, kodya });

		List<String> list = new ArrayList<String>();

		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}

		cursor.close();

		return list;
	}

	public List<String> getKelurahanList(String aoCode, String kodya, String kecamatan) {
		String sql = "SELECT DISTINCT KELURAHAN FROM TBL_MS_USER_ZIP WHERE AO_CODE = ? AND KODYA_KAB = ? AND KECAMATAN = ?";

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] { aoCode, kodya, kecamatan });

		List<String> list = new ArrayList<String>();

		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}

		cursor.close();

		return list;
	}
	
	public static void deleteUserZip(SQLiteDatabase db, String aoCode) {
		db.execSQL("DELETE FROM TBL_MS_USER_ZIP WHERE AO_CODE = ?", new String[] { aoCode });
	}

	public List<String> getPostalCodeList(String aoCode, String kodya, String kecamatan, String kelurahan) {
		String sql = "SELECT DISTINCT POSTAL_CODE FROM TBL_MS_USER_ZIP "
				+ "WHERE AO_CODE = ? AND KODYA_KAB = ? AND KECAMATAN = ? AND KELURAHAN = ?";

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] { aoCode, kodya, kecamatan,
				kelurahan });

		List<String> list = new ArrayList<String>();

		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}

		cursor.close();

		return list;

	}

	public static String getMaxDate(SQLiteDatabase db, String aoCode) {		
		Cursor cursor = db.rawQuery(
				"SELECT MAX(UPDATE_DATE) UPDATE_DATE FROM TBL_MS_USER_ZIP WHERE AO_CODE = ?", new String[] { aoCode });

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}
		
		String result = cursor.getString(cursor.getColumnIndex("UPDATE_DATE"));		
		cursor.close();

		return result;
	}
}
